{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# 用python方式实现rfm模型\n",
    "指标说明:\n",
    "    R:会员最近一次消费(用后一次消费距今天日期进行指标化,数值越低越好)\n",
    "    F:会员消费频率\n",
    "    M:会员消费金额\n",
    "计算逻辑:\n",
    "    1.获取rfm值,并且获取三个值的中位数\n",
    "    2.利用对应值和中位数的计算比较或得目标模型值的高或者低\n",
    "计算结果:\n",
    "用户分类|R|F|M\n",
    "重要价值客户|高|高|高\n",
    "重要发展客户|高|低|高\n",
    "重要保持客户|低|高|高\n",
    "重要挽留客户|低|低|高\n",
    "一般价值客户|高|高|低\n",
    "一般发展客户|高|低|低\n",
    "一般保持客户|低|高|低\n",
    "一般挽留用户|低|低|低"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "70ac0156909dde4d"
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import configparser  #读取配置文件标准库\n",
    "from sqlalchemy import create_engine"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-04-10T06:26:17.011312Z",
     "start_time": "2024-04-10T06:26:17.006164Z"
    }
   },
   "id": "788023e7e1005b13",
   "execution_count": 14
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "       id        order_no  member_id  level_id voucher_date  sale_amount\n962  3031  17122301257703         63         4   2023-09-21       798.26\n358  2427  17122298963199         36         4   2023-09-03       942.70\n120  2189  17122298608694         33         4   2023-08-27       182.12\n846  2915  17122300819074         68         3   2023-08-27       625.93\n155  2224  17122298688271         89         5   2024-02-28       701.99\n984  3053  17122301345879         50         3   2023-10-30       597.35\n850  2919  17122300844655         83         4   2023-09-30       344.29\n335  2404  17122298938952         74         5   2024-01-20       520.72\n799  2868  17122300623645         97         5   2023-08-23       514.07\n264  2333  17122298832769         34         4   2023-06-19       923.81",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>id</th>\n      <th>order_no</th>\n      <th>member_id</th>\n      <th>level_id</th>\n      <th>voucher_date</th>\n      <th>sale_amount</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>962</th>\n      <td>3031</td>\n      <td>17122301257703</td>\n      <td>63</td>\n      <td>4</td>\n      <td>2023-09-21</td>\n      <td>798.26</td>\n    </tr>\n    <tr>\n      <th>358</th>\n      <td>2427</td>\n      <td>17122298963199</td>\n      <td>36</td>\n      <td>4</td>\n      <td>2023-09-03</td>\n      <td>942.70</td>\n    </tr>\n    <tr>\n      <th>120</th>\n      <td>2189</td>\n      <td>17122298608694</td>\n      <td>33</td>\n      <td>4</td>\n      <td>2023-08-27</td>\n      <td>182.12</td>\n    </tr>\n    <tr>\n      <th>846</th>\n      <td>2915</td>\n      <td>17122300819074</td>\n      <td>68</td>\n      <td>3</td>\n      <td>2023-08-27</td>\n      <td>625.93</td>\n    </tr>\n    <tr>\n      <th>155</th>\n      <td>2224</td>\n      <td>17122298688271</td>\n      <td>89</td>\n      <td>5</td>\n      <td>2024-02-28</td>\n      <td>701.99</td>\n    </tr>\n    <tr>\n      <th>984</th>\n      <td>3053</td>\n      <td>17122301345879</td>\n      <td>50</td>\n      <td>3</td>\n      <td>2023-10-30</td>\n      <td>597.35</td>\n    </tr>\n    <tr>\n      <th>850</th>\n      <td>2919</td>\n      <td>17122300844655</td>\n      <td>83</td>\n      <td>4</td>\n      <td>2023-09-30</td>\n      <td>344.29</td>\n    </tr>\n    <tr>\n      <th>335</th>\n      <td>2404</td>\n      <td>17122298938952</td>\n      <td>74</td>\n      <td>5</td>\n      <td>2024-01-20</td>\n      <td>520.72</td>\n    </tr>\n    <tr>\n      <th>799</th>\n      <td>2868</td>\n      <td>17122300623645</td>\n      <td>97</td>\n      <td>5</td>\n      <td>2023-08-23</td>\n      <td>514.07</td>\n    </tr>\n    <tr>\n      <th>264</th>\n      <td>2333</td>\n      <td>17122298832769</td>\n      <td>34</td>\n      <td>4</td>\n      <td>2023-06-19</td>\n      <td>923.81</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "config = configparser.ConfigParser()\n",
    "config.read(\"..\\\\..\\\\..\\\\资源配置\\\\数据库配置\\\\config.ini\")\n",
    "# 获取数据库配置\n",
    "db_config = {\n",
    "    'user': config['mysql-db4free-database']['USER'],\n",
    "    'password': config['mysql-db4free-database']['PASSWORD'],\n",
    "    'host': config['mysql-db4free-database']['HOST'],\n",
    "    'port': config['mysql-db4free-database']['PORT'],\n",
    "    'database': config['mysql-db4free-database']['DBNAME']\n",
    "}\n",
    "#定义数据库连接\n",
    "engine = create_engine(\n",
    "    f\"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db_config['database']}\")\n",
    "sql_query = \"SELECT * FROM rfm_orders\"\n",
    "df = pd.read_sql_query(sql_query, engine )\n",
    "display(df.sample(10))"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-04-10T06:32:24.686189Z",
     "start_time": "2024-04-10T06:32:17.416790Z"
    }
   },
   "id": "48033962c63da76d",
   "execution_count": 26
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 开始生成模型"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "f6619393a33a56c6"
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "       id        order_no  member_id  level_id voucher_date  sale_amount   天数\n588  2657  17122299787032         41         4   2023-12-22       532.89  110\n319  2388  17122298912686         73         5   2023-10-08       778.86  185\n667  2736  17122300092932         22         5   2023-09-16       873.56  207\n469  2538  17122299315454         50         4   2024-02-18       630.21   52\n701  2770  17122300231662         56         2   2023-04-29       915.06  347\n957  3026  17122301233627         85         2   2023-05-26       623.68  320\n35   2104  17122298505048         73         1   2023-08-18       803.37  236\n375  2444  17122298993616         18         3   2023-11-20       400.18  142\n360  2429  17122298961543         63         4   2023-04-24       660.17  352\n616  2685  17122299893444         74         4   2023-09-24       554.00  199",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>id</th>\n      <th>order_no</th>\n      <th>member_id</th>\n      <th>level_id</th>\n      <th>voucher_date</th>\n      <th>sale_amount</th>\n      <th>天数</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>588</th>\n      <td>2657</td>\n      <td>17122299787032</td>\n      <td>41</td>\n      <td>4</td>\n      <td>2023-12-22</td>\n      <td>532.89</td>\n      <td>110</td>\n    </tr>\n    <tr>\n      <th>319</th>\n      <td>2388</td>\n      <td>17122298912686</td>\n      <td>73</td>\n      <td>5</td>\n      <td>2023-10-08</td>\n      <td>778.86</td>\n      <td>185</td>\n    </tr>\n    <tr>\n      <th>667</th>\n      <td>2736</td>\n      <td>17122300092932</td>\n      <td>22</td>\n      <td>5</td>\n      <td>2023-09-16</td>\n      <td>873.56</td>\n      <td>207</td>\n    </tr>\n    <tr>\n      <th>469</th>\n      <td>2538</td>\n      <td>17122299315454</td>\n      <td>50</td>\n      <td>4</td>\n      <td>2024-02-18</td>\n      <td>630.21</td>\n      <td>52</td>\n    </tr>\n    <tr>\n      <th>701</th>\n      <td>2770</td>\n      <td>17122300231662</td>\n      <td>56</td>\n      <td>2</td>\n      <td>2023-04-29</td>\n      <td>915.06</td>\n      <td>347</td>\n    </tr>\n    <tr>\n      <th>957</th>\n      <td>3026</td>\n      <td>17122301233627</td>\n      <td>85</td>\n      <td>2</td>\n      <td>2023-05-26</td>\n      <td>623.68</td>\n      <td>320</td>\n    </tr>\n    <tr>\n      <th>35</th>\n      <td>2104</td>\n      <td>17122298505048</td>\n      <td>73</td>\n      <td>1</td>\n      <td>2023-08-18</td>\n      <td>803.37</td>\n      <td>236</td>\n    </tr>\n    <tr>\n      <th>375</th>\n      <td>2444</td>\n      <td>17122298993616</td>\n      <td>18</td>\n      <td>3</td>\n      <td>2023-11-20</td>\n      <td>400.18</td>\n      <td>142</td>\n    </tr>\n    <tr>\n      <th>360</th>\n      <td>2429</td>\n      <td>17122298961543</td>\n      <td>63</td>\n      <td>4</td>\n      <td>2023-04-24</td>\n      <td>660.17</td>\n      <td>352</td>\n    </tr>\n    <tr>\n      <th>616</th>\n      <td>2685</td>\n      <td>17122299893444</td>\n      <td>74</td>\n      <td>4</td>\n      <td>2023-09-24</td>\n      <td>554.00</td>\n      <td>199</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#此步骤主要是datetime和Timestamp转换让人非常烦躁\n",
    "df[\"天数\"] = (pd.to_datetime(\"today\")-pd.to_datetime(df[\"voucher_date\"])).dt.days\n",
    "display(df.sample(10))"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-04-10T06:30:28.623898Z",
     "start_time": "2024-04-10T06:30:28.605875Z"
    }
   },
   "id": "dde5af8fa737a19",
   "execution_count": 23
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "           天数  order_no_count  sale_amount_sum R_band F_band M_band    判定结果\nmember_id                                                                  \n11          1               9          4570.83      高      低      低  一般发展客户\n12         56               9          4780.62      低      低      低  一般挽留用户\n13         46               7          4349.02      低      低      低  一般挽留用户\n14         52               9          4472.48      低      低      低  一般挽留用户\n15         87              11          4472.68      低      高      低  一般保持客户\n...        ..             ...              ...    ...    ...    ...     ...\n96         43              14          7369.73      低      高      高  重要保持客户\n97          1              11          4784.83      高      高      低  一般价值客户\n98         11              17          9764.24      高      高      高  重要价值客户\n99          4               8          4339.52      高      低      低  一般发展客户\n100        25              11          4897.80      高      高      低  一般价值客户\n\n[90 rows x 7 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>天数</th>\n      <th>order_no_count</th>\n      <th>sale_amount_sum</th>\n      <th>R_band</th>\n      <th>F_band</th>\n      <th>M_band</th>\n      <th>判定结果</th>\n    </tr>\n    <tr>\n      <th>member_id</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>11</th>\n      <td>1</td>\n      <td>9</td>\n      <td>4570.83</td>\n      <td>高</td>\n      <td>低</td>\n      <td>低</td>\n      <td>一般发展客户</td>\n    </tr>\n    <tr>\n      <th>12</th>\n      <td>56</td>\n      <td>9</td>\n      <td>4780.62</td>\n      <td>低</td>\n      <td>低</td>\n      <td>低</td>\n      <td>一般挽留用户</td>\n    </tr>\n    <tr>\n      <th>13</th>\n      <td>46</td>\n      <td>7</td>\n      <td>4349.02</td>\n      <td>低</td>\n      <td>低</td>\n      <td>低</td>\n      <td>一般挽留用户</td>\n    </tr>\n    <tr>\n      <th>14</th>\n      <td>52</td>\n      <td>9</td>\n      <td>4472.48</td>\n      <td>低</td>\n      <td>低</td>\n      <td>低</td>\n      <td>一般挽留用户</td>\n    </tr>\n    <tr>\n      <th>15</th>\n      <td>87</td>\n      <td>11</td>\n      <td>4472.68</td>\n      <td>低</td>\n      <td>高</td>\n      <td>低</td>\n      <td>一般保持客户</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>96</th>\n      <td>43</td>\n      <td>14</td>\n      <td>7369.73</td>\n      <td>低</td>\n      <td>高</td>\n      <td>高</td>\n      <td>重要保持客户</td>\n    </tr>\n    <tr>\n      <th>97</th>\n      <td>1</td>\n      <td>11</td>\n      <td>4784.83</td>\n      <td>高</td>\n      <td>高</td>\n      <td>低</td>\n      <td>一般价值客户</td>\n    </tr>\n    <tr>\n      <th>98</th>\n      <td>11</td>\n      <td>17</td>\n      <td>9764.24</td>\n      <td>高</td>\n      <td>高</td>\n      <td>高</td>\n      <td>重要价值客户</td>\n    </tr>\n    <tr>\n      <th>99</th>\n      <td>4</td>\n      <td>8</td>\n      <td>4339.52</td>\n      <td>高</td>\n      <td>低</td>\n      <td>低</td>\n      <td>一般发展客户</td>\n    </tr>\n    <tr>\n      <th>100</th>\n      <td>25</td>\n      <td>11</td>\n      <td>4897.80</td>\n      <td>高</td>\n      <td>高</td>\n      <td>低</td>\n      <td>一般价值客户</td>\n    </tr>\n  </tbody>\n</table>\n<p>90 rows × 7 columns</p>\n</div>"
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "rfm_scores = df.groupby('member_id').agg({\n",
    "    '天数': 'min',\n",
    "    'order_no': 'count',\n",
    "    'sale_amount': 'sum'\n",
    "}).rename(columns={'order_no':'order_no_count','sale_amount':'sale_amount_sum'})\n",
    "rfm_scores['R_band']=np.where(rfm_scores[\"天数\"]<=rfm_scores[\"天数\"].median(),'高','低')\n",
    "rfm_scores['F_band']=np.where(rfm_scores[\"order_no_count\"]>=rfm_scores[\"order_no_count\"].median(),'高','低')\n",
    "rfm_scores['M_band']=np.where(rfm_scores[\"sale_amount_sum\"]>=rfm_scores[\"sale_amount_sum\"].median(),'高','低')\n",
    "rfm_scores['判定结果'] = ''\n",
    "rfm_scores.loc[(rfm_scores['R_band']==\"高\")&(rfm_scores['F_band']==\"高\")&(rfm_scores['M_band']==\"高\"), '判定结果']='重要价值客户'\n",
    "rfm_scores.loc[(rfm_scores['R_band']==\"高\")&(rfm_scores['F_band']==\"低\")&(rfm_scores['M_band']==\"高\"), '判定结果']='重要发展客户'\n",
    "rfm_scores.loc[(rfm_scores['R_band']==\"低\")&(rfm_scores['F_band']==\"高\")&(rfm_scores['M_band']==\"高\"), '判定结果']='重要保持客户'\n",
    "rfm_scores.loc[(rfm_scores['R_band']==\"低\")&(rfm_scores['F_band']==\"低\")&(rfm_scores['M_band']==\"高\"), '判定结果']='重要挽留客户'\n",
    "rfm_scores.loc[(rfm_scores['R_band']==\"高\")&(rfm_scores['F_band']==\"高\")&(rfm_scores['M_band']==\"低\"), '判定结果']='一般价值客户'\n",
    "rfm_scores.loc[(rfm_scores['R_band']==\"高\")&(rfm_scores['F_band']==\"低\")&(rfm_scores['M_band']==\"低\"), '判定结果']='一般发展客户'\n",
    "rfm_scores.loc[(rfm_scores['R_band']==\"低\")&(rfm_scores['F_band']==\"高\")&(rfm_scores['M_band']==\"低\"), '判定结果']='一般保持客户'\n",
    "rfm_scores.loc[(rfm_scores['R_band']==\"低\")&(rfm_scores['F_band']==\"低\")&(rfm_scores['M_band']==\"低\"), '判定结果']='一般挽留用户'\n",
    "display(rfm_scores)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-04-05T02:09:28.185748Z",
     "start_time": "2024-04-05T02:09:28.111750Z"
    }
   },
   "id": "335f8b3971331bc2",
   "execution_count": 27
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
